package com.greateamd.timetableasy.server.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.google.gwt.user.datepicker.client.CalendarUtil;
import com.greateamd.timetableasy.client.domain.AuthType;
import com.greateamd.timetableasy.domain.Auth;
import com.greateamd.timetableasy.domain.Event;
import com.greateamd.timetableasy.domain.User;
import com.greateamd.timetableasy.server.ServerUtil;
import com.greateamd.timetableasy.server.dao.UserDAO;
import com.greateamd.timetableasy.server.dao.util.AbstractHibernateDAO;



@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED, readOnly = false)	
@Repository("userDAO")
public class UserDAOImpl extends AbstractHibernateDAO<User,String> implements UserDAO {

	public UserDAOImpl() {
		super(new User());
	}

	@Override
	public void save(User user) throws HibernateException, SQLException {
		String passwordSansMD = user.getPassword();
		String passwordCrypte = ServerUtil.md5(passwordSansMD);
		user.setPassword(passwordCrypte);
		super.save(user);
	}
	
	@Override
	public List<User> getAllCampusManager() {
		logger.debug("Get all campus manager from db");
		HibernateCallback callback = new HibernateCallback() {

			public Object doInHibernate(Session session) throws HibernateException,SQLException {
				Criteria critia = session.createCriteria(User.class);
				Criteria subCritia = critia.createCriteria("auths");
				subCritia.add(Restrictions.eq("authority", AuthType.ROLE_CAMPUS_MANAGER.name()));
				List<User> result = subCritia.list();
				return result;
	        }
	    };
	    return (List) getHibernateTemplate().execute(callback);
	}

	@Override
	public List<User> getUserWithoutPromotion() {
		logger.debug("Get all user without promotion from db");
		HibernateCallback callback = new HibernateCallback() {

			public Object doInHibernate(Session session) throws HibernateException,SQLException {
				String requeteFrom = User.class.getName();
        		List result = session.createQuery("from "+requeteFrom+" user where user.promotion is null").list();
				return result;
	        }
	    };
	    return (List) getHibernateTemplate().execute(callback);
	}

	@Override
	public List<User> getAllTeachers() {
		logger.debug("Get all teachers from db");
		HibernateCallback callback = new HibernateCallback() {

			public Object doInHibernate(Session session) throws HibernateException,SQLException {
				Criteria critia = session.createCriteria(User.class);
				Criteria subCritia = critia.createCriteria("auths");
				subCritia.add(Restrictions.eq("authority", AuthType.ROLE_TEACHER.name()));
				List<User> result = subCritia.list();
				return result;
	        }
	    };
	    return (List) getHibernateTemplate().execute(callback);
	}

	@Override
	public List<Event> getPlanning(final User user, final Date from, final Date to) {
		logger.debug("Get "+user.getUsername()+"'s user planning from db");
		HibernateCallback callback = new HibernateCallback() {

			public Object doInHibernate(Session session) throws HibernateException,SQLException {
				final List<Event> events = new ArrayList<Event>();
				//Marche pas
//				String hql = "SELECT distinct(event) " +
//				 "FROM User user " +
//				 "WHERE EXISTS ( " +
//				 		"SELECT user.events e " +
//				 		"FROM Event e " +
//				 		"WHERE e.startDate BETWEEN :from AND :to ) "+
//				 "order by event.startDate";
				//Marche
//				String hql = "SELECT distinct(event) " +
//				 "FROM User user, Promotion promotion " +
//				 "INNER JOIN user.events event " +
//				 	"WITH event.startDate >= :from AND event.startDate <= :to "+
//				 "order by event.startDate";

				
				//get user events
				String hql = "SELECT distinct(event) " +
				 "FROM User user " +
				 "JOIN user.events event " +
				 	"WITH event.startDate >= :from AND event.startDate <= :to "+
			 	 "WHERE user.username = :username " +
				 "ORDER BY event.startDate";
				Query query = session.createQuery(hql);
				query.setParameter("username", user.getUsername());
				query.setDate("from", from);
				query.setDate("to", to);
				List list = query.list();
				events.addAll(list);


				//get user.promotion.studyPeriods events
				hql = "SELECT distinct(event) " +
				 "FROM User user " +
				 "JOIN user.promotion.studyPeriods sp JOIN sp.events event " +
				 	"WITH event.startDate >= :from AND event.startDate <= :to "+
			 	 "WHERE user.username = :username " +
				 "order by event.startDate";
				query = session.createQuery(hql);
				query.setParameter("username", user.getUsername());
				query.setDate("from", from);
				query.setDate("to", to);
				list = query.list();
				events.addAll(list);
				
				//get user.promotion.courses events
				hql = "SELECT distinct(event) " +
				 "FROM User user " +
				 "JOIN user.promotion.courses course JOIN course.events event " +
				 	"WITH event.startDate >= :from AND event.startDate <= :to "+
			 	 "WHERE user.username = :username " +
				 "order by event.startDate";
				query = session.createQuery(hql);
				query.setParameter("username", user.getUsername());
				query.setDate("from", from);
				query.setDate("to", to);
				list = query.list();
				events.addAll(list);
				
				//get user.promotion events
				hql = "SELECT distinct(event) " +
				 "FROM User user " +
				 "JOIN user.promotion p JOIN p.events event " +
				 	"WITH event.startDate >= :from AND event.startDate <= :to "+
			 	 "WHERE user.username = :username " +
				 "order by event.startDate";
				query = session.createQuery(hql);
				query.setParameter("username", user.getUsername());
				query.setDate("from", from);
				query.setDate("to", to);
				list = query.list();
				events.addAll(list);
				
				//get user.promotion.campus events
				hql = "SELECT distinct(event) " +
				 "FROM User user " +
				 "JOIN user.promotion.campus c JOIN c.events event " +
				 	"WITH event.startDate >= :from AND event.startDate <= :to "+
			 	 "WHERE user.username = :username " +
				 "order by event.startDate";
				query = session.createQuery(hql);
				query.setParameter("username", user.getUsername());
				query.setDate("from", from);
				query.setDate("to", to);
				list = query.list();
				events.addAll(list);
				
				//get user.promotion.campus.university events
				hql = "SELECT distinct(event) " +
				 "FROM User user " +
				 "JOIN user.promotion.campus.university u JOIN u.events event " +
				 	"WITH event.startDate >= :from AND event.startDate <= :to "+
			 	 "WHERE user.username = :username " +
				 "order by event.startDate";
				query = session.createQuery(hql);
				query.setParameter("username", user.getUsername());
				query.setDate("from", from);
				query.setDate("to", to);
				list = query.list();
				events.addAll(list);

				ServerUtil.sortStartDateList(events);
				
				return events;
	        }
	    };
	    return (List) getHibernateTemplate().execute(callback);
	}
	

	@Override
	public Collection<Event> getTeacherPlanning(final User user, final Date from, final Date to) {
		final Set<Event> events = new HashSet<Event>();
		
		logger.debug("Get "+user.getUsername()+"'s user planning from db");
		HibernateCallback callback = new HibernateCallback() {

			public Object doInHibernate(Session session) throws HibernateException,SQLException {
				//get events where he is the dispenser
				String hql = "SELECT distinct(event) " +
				 "FROM Event event " +
			 	 "WHERE event.dispenser = :username " +
				 	 "AND event.startDate >= :from " +
				 	 "AND event.startDate <= :to "+
				 "ORDER BY event.startDate";
				Query query = session.createQuery(hql);
				query.setParameter("username", user.getUsername());
				query.setDate("from", from);
				query.setDate("to", to);
				return query.list();
	        }
	    };
	    events.addAll((List) getHibernateTemplate().execute(callback));
	    events.addAll(getPlanning(user, from, to));
		return events;
	}
	
	private void addToEventList(Collection<Event> c,Collection<Event> list,Date from,Date to){
		for(Event e : list){
			int daysBetweenEvent = CalendarUtil.getDaysBetween(from, e.getStartDate());
			int daysAlpha = CalendarUtil.getDaysBetween(from, to);
			if( daysBetweenEvent >= 0 && (daysBetweenEvent - daysAlpha) <= 0 ){
				c.add(e);
			}
		}
	}

	@Override
	public void addUserAuth(final User user, final AuthType auth) {
		getHibernateTemplate().execute(new HibernateCallback<Void>() {
	         public Void doInHibernate(Session session) throws HibernateException,SQLException {
	        	 final User who = (User) session.get(User.class, user.getUsername());
	        	 who.addAuth(new Auth(user,auth));
	        	 session.update(who);
	        	 return null;
	         }
		});
	}

	@Override
	public void removeUserAuth(final User user, final AuthType auth) {
		getHibernateTemplate().execute(new HibernateCallback<Void>() {
	         public Void doInHibernate(Session session) throws HibernateException,SQLException {
	        	 final User who = (User) session.get(User.class, user.getUsername());
	        	 Criteria critia = session.createCriteria(Auth.class);
	        	 critia.add(Restrictions.eq("user",who));
	        	 critia.add(Restrictions.eq("authority",auth.name()));
	        	 Auth l = (Auth) critia.uniqueResult();
	        	 who.getAuths().remove(l);
	        	 session.delete(l);
	        	 return null;
	         }
		});
	}

	@Override
	public boolean getTeacherDisponibility(final Integer excluseThisEventId,final String teacherUsername, final Date from, final int duration) throws HibernateException, SQLException {
		return getHibernateTemplate().execute(new HibernateCallback<Boolean>() {

			public Boolean doInHibernate(Session session) throws HibernateException,SQLException {
				//get campus events
				String hql = "SELECT distinct(event) " +
				 "FROM Event event " +
			 	 "WHERE event.dispenser LIKE :dispenser " +
			 	 	"AND day(event.startDate) = day(:from) ";
				if(excluseThisEventId != null){
					hql += " AND event.id != :excludeEventId";
				}
				final Query query = session.createQuery(hql);
				query.setParameter("dispenser", teacherUsername );
				query.setDate("from", from);
				if(excluseThisEventId != null){
					query.setParameter("excludeEventId", excluseThisEventId );
				}
				final List<Event> list = query.list();
				
				for(Event event : list){
					final int startHour = event.getStartDate().getHours();
					final int endHour = startHour+event.getDuration();
					final int startHourRequest = from.getHours();
					final int endHourRequest = startHourRequest+duration;
					if( startHourRequest <= startHour && startHour < endHourRequest ){
						return false;
					} else if( startHourRequest < endHour && endHour <= endHourRequest ){
						return false;
					}
				}
				return true;
	        }
	    });
	}

	@Override
	public boolean addEvent(final Event event) throws HibernateException, SQLException, IllegalAccessException {
		final User loggedUser = getHibernateTemplate().execute(new HibernateCallback<User>() {
			@Override
			public User doInHibernate(Session session) throws HibernateException, SQLException {
				final String username = SecurityContextHolder.getContext().getAuthentication().getName();
				return (User) session.get(User.class, username);
			}});
		loggedUser.addEvent(event);
		update(loggedUser);
		return true;
	}
}
